/**
 * Program   :   SqlParser.java
 * Author    :   shadow guo
 * Create    :   2013-6-19 上午10:30:01
 *
 * Copyright 2008 by Tongfu Information Technology Co. Ltd
 * All rights reserved.
 *
 * This software is the confidential and proprietary information
 * of Tongfu Information Technology Co. Ltd. ("Confidential Information").  You
 * shall not disclose such Confidential Information and shall use
 * it only in accordance with the terms of the license agreement
 * you entered into with Tongfu Information Technology Co. Ltd.
 * 
 */
package com.tongfusoft.web.utils;

import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.lang3.StringUtils;

public class SqlParser {
	/**
	 * 逗号
	 */
	private static final String Comma = ",";

	/**
	 * 四个空格
	 */
	private static final String FourSpace = "    ";

	/**
	 * 是否单行显示字段，表，条件的标识量
	 */
	private static boolean isSingleLine = true;

	/**
	 * 待解析的SQL语句
	 */
	private String sql;

	/**
	 * SQL中选择的列
	 */
	private String cols;
	
	private List<String> colList = new ArrayList<String>(20);

	/**
	 * SQL中查找的表
	 */
	private String tables;

	/**
	 * 查找条件
	 */
	private String conditions;

	/**
	 * Group By的字段
	 */
	private String groupCols;

	/**
	 * Order by的字段
	 */
	private String orderCols;

	/**
	 * 构造函数 功能：传入构造函数，解析成字段，表，条件等
	 * 
	 * @param sql
	 *            ：传入的SQL语句
	 */
	public SqlParser(String sql) {
		this.sql = sql.trim();
		convertSql();
		parser();
	}
	
	private void parserColsList(){
		this.colList.clear();
		String[] columns = StringUtils.split(this.getCols(), Comma);
		for(int i=0;i<columns.length;i++){
			if(columns[i].contains(" AS ")){
				int index = columns[i].indexOf(" AS ");
				this.colList.add(columns[i].substring(index + 4).trim());
			}else{
				this.colList.add(columns[i].trim());
			}
		}
	}
	
	
	public List<String> getColList() {
		return colList;
	}

	public void setColList(List<String> colList) {
		this.colList = colList;
	}

	public SqlParser(){
		super();
	}
	
	
	public void parser(){
		if(StringUtils.isNotEmpty(this.getSql())){
			parseCols();
			parserColsList();
			parseTables();
			parseConditions();
			parseGroupCols();
			parseOrderCols();
		}
	}
	
	private void convertSql(){
		sql = StringUtils.replace(this.getSql(), "\n", " ");
		sql = StringUtils.replace(this.getSql(), "\r", " ");
	}
	

	/**
	 * 解析选择的列
	 * 
	 */
	private void parseCols() {
		String regex = "(SELECT)(.+)(FROM)";
		cols = getMatchedString(regex, sql);
	}

	/**
	 * 解析选择的表
	 * 
	 */
	private void parseTables() {
		String regex = "";

		if (isContains(sql, "\\s+WHERE\\s+")) {
			regex = "(FROM)(.+)(WHERE)";
		} else if(isContains(sql, "\\s+ORDER\\s+BY\\s+")){
			regex = "(FROM)(.+)(ORDER\\s+BY)";
		} else if(isContains(sql, "\\s+GROUP\\s+BY\\s+")){
			regex = "(FROM)(.+)(GROUP\\s+BY)";
		} else{
			regex = "(from)(.+)($)";
		}

		tables = getMatchedString(regex, sql);
	}

	/**
	 * 解析查找条件
	 * 
	 */
	private void parseConditions() {
		String regex = "";

		if (isContains(sql, "\\s+WHERE\\s+")) {
			// 包括Where，有条件

			if (isContains(sql, "GROUP\\s+BY")) {
				// 条件在where和group by之间
				regex = "(WHERE)(.+)(GROUP\\s+BY)";
			} else if (isContains(sql, "ORDER\\s+BY")) {
				// 条件在where和order by之间
				regex = "(WHERE)(.+)(ORDER\\s+BY)";
			} else {
				// 条件在where到字符串末尾
				regex = "(WHERE)(.+)($)";
			}
		} else {
			// 不包括where则条件无从谈起，返回即可
			return;
		}

		conditions = getMatchedString(regex, sql);
	}

	/**
	 * 解析GroupBy的字段
	 * 
	 */
	private void parseGroupCols() {
		String regex = "";

		if (isContains(sql, "GROUP\\s+BY")) {
			// 包括GroupBy，有分组字段

			if (isContains(sql, "ORDER\\s+BY")) {
				// group by 后有order by
				regex = "(GROUP\\s+BY)(.+)(ORDER\\s+BY)";
			} else {
				// group by 后无order by
				regex = "(GROUP\\s+BY)(.+)($)";
			}
		} else {
			// 不包括GroupBy则分组字段无从谈起，返回即可
			return;
		}

		groupCols = getMatchedString(regex, sql);
	}

	/**
	 * 解析OrderBy的字段
	 * 
	 */
	private void parseOrderCols() {
		String regex = "";

		if (isContains(sql, "ORDER\\s+BY")) {
			// 包括GroupBy，有分组字段
			regex = "(ORDER\\s+BY)(.+)($)";
		} else {
			// 不包括GroupBy则分组字段无从谈起，返回即可
			return;
		}

		orderCols = getMatchedString(regex, sql);
	}

	/**
	 * 从文本text中找到regex首次匹配的字符串，不区分大小写
	 * 
	 * @param regex
	 *            ： 正则表达式
	 * @param text
	 *            ：欲查找的字符串
	 * @return regex首次匹配的字符串，如未匹配返回空
	 */
	private static String getMatchedString(String regex, String text) {
		Pattern pattern = Pattern.compile(regex, Pattern.CASE_INSENSITIVE);

		Matcher matcher = pattern.matcher(text);

		while (matcher.find()) {
			return matcher.group(2);
		}

		return null;
	}

	/**
	 * 看word是否在lineText中存在，支持正则表达式
	 * 
	 * @param lineText
	 * @param word
	 * @return
	 */
	private static boolean isContains(String lineText, String word) {
		Pattern pattern = Pattern.compile(word, Pattern.CASE_INSENSITIVE);
		Matcher matcher = pattern.matcher(lineText);
		return matcher.find();
	}

	public String toString() {
		// 无法解析则原样返回
		if (cols == null && tables == null && conditions == null
				&& groupCols == null && orderCols == null) {
			return sql;
		}

		StringBuffer sb = new StringBuffer();
		sb.append("原SQL为" + sql + "\n");
		sb.append("解析后的SQL为\n");

		for (String str : getParsedSqlList()) {
			sb.append(str);
		}

		sb.append("\n");

		return sb.toString();
	}

	/**
	 * 在分隔符后加上回车
	 * 
	 * @param str
	 * @param splitStr
	 * @return
	 */
	private static String getAddEnterStr(String str, String splitStr) {
		Pattern p = Pattern.compile(splitStr, Pattern.CASE_INSENSITIVE);

		// 用Pattern类的matcher()方法生成一个Matcher对象
		Matcher m = p.matcher(str);
		StringBuffer sb = new StringBuffer();

		// 使用find()方法查找第一个匹配的对象
		boolean result = m.find();

		// 使用循环找出模式匹配的内容替换之,再将内容加到sb里
		while (result) {
			m.appendReplacement(sb, m.group(0) + "\n     ");
			result = m.find();
		}
		// 最后调用appendTail()方法将最后一次匹配后的剩余字符串加到sb里；
		m.appendTail(sb);

		return FourSpace + sb.toString();
	}

	/**
	 * 取得解析的SQL字符串列表
	 * 
	 * @return
	 */
	public List<String> getParsedSqlList() {
		List<String> sqlList = new ArrayList<String>();

		// 无法解析则原样返回
		if (cols == null && tables == null && conditions == null
				&& groupCols == null && orderCols == null) {
			sqlList.add(sql);
			return sqlList;
		}

		if (cols != null) {
			sqlList.add("SELECT\n");
			if (isSingleLine) {
				sqlList.add(getAddEnterStr(cols, Comma));
			} else {
				sqlList.add(FourSpace + cols);
			}
		}

		if (tables != null) {
			sqlList.add(" \nFROM\n");

			if (isSingleLine) {
				sqlList.add(getAddEnterStr(tables, Comma));
			} else {
				sqlList.add(FourSpace + tables);
			}
		}

		if (conditions != null) {
			sqlList.add(" \nWHERE\n");

			if (isSingleLine) {
				sqlList.add(getAddEnterStr(conditions, "(AND|OR)"));
			} else {
				sqlList.add(FourSpace + conditions);
			}
		}

		if (groupCols != null) {
			sqlList.add(" \nGROUP BY\n");

			if (isSingleLine) {
				sqlList.add(getAddEnterStr(groupCols, Comma));
			} else {
				sqlList.add(FourSpace + groupCols);
			}
		}

		if (orderCols != null) {
			sqlList.add(" \nORDER BY\n");

			if (isSingleLine) {
				sqlList.add(getAddEnterStr(orderCols, Comma));
			} else {
				sqlList.add(FourSpace + orderCols);
			}
		}

		return sqlList;
	}

	/**
	 * 设置是否单行显示表，字段，条件等
	 * 
	 * @param isSingleLine
	 */
	public static void setSingleLine(boolean isSingleLine) {
		SqlParser.isSingleLine = isSingleLine;
	}
	
	public String getSql() {
		return sql;
	}

	public void setSql(String sql) {
		this.sql = sql;
		convertSql();
	}

	public String getCols() {
		return cols;
	}

	public void setCols(String cols) {
		this.cols = cols;
	}

	public String getTables() {
		return tables;
	}

	public void setTables(String tables) {
		this.tables = tables;
	}

	public String getConditions() {
		return conditions;
	}

	public void setConditions(String conditions) {
		this.conditions = conditions;
	}

	public String getGroupCols() {
		return groupCols;
	}

	public void setGroupCols(String groupCols) {
		this.groupCols = groupCols;
	}

	public String getOrderCols() {
		return orderCols;
	}

	public void setOrderCols(String orderCols) {
		this.orderCols = orderCols;
	}

	/**
	 * 测试
	 * 
	 * @param args
	 */
	public static void main(String[] args) {
		List<String> ls = new ArrayList<String>();
		/*ls.add("select * from dual");
		ls.add("SELECT * frOm dual");
		ls.add("Select C1,c2 From tb");
		ls.add("select c1,c2 from tb");
		ls.add("select count(*) as num ,sum(*) as test from t1");
		ls.add("select c1,c2,c3 from t1 where condi1=1 ");
		ls.add("Select c1,c2,c3 From t1 Where condi1=1 ");
		ls.add("select c1,c2,c3 from t1,t2 where condi3=3 or condi4=5 order   by o1,o2");
		ls.add("Select c1,c2,c3 from t1,t2 Where condi3=3 or condi4=5 Order   by o1,o2");
		ls.add("select c1,c2,c3 from t1,t2,t3 where condi1=5 and condi6=6 or condi7=7 group  by g1,g2");
		ls.add("Select c1,c2,c3 From t1,t2,t3 Where condi1=5 and condi6=6 or condi7=7 Group  by g1,g2");
		ls.add("Select c1,c2,c3 From t1,t2,t3 Where condi1=5 and condi6=6 or condi7=7 Group  by g1,g2,g3 order  by g2,g3");
*/
		ls.add("select a.res_name,a.res_desc,b.user_name,b.real_name from t_resources a inner join t_users b on a.created_by = b.user_id where 1=1 order by aa".toUpperCase());
		ls.add("select res_id,res_name,res_desc from t_resources order by res_name".toUpperCase());
		for (String sql : ls) {
			System.out.println(new SqlParser(sql));
			// System.out.println(sql);
		}
	}

}
